module SearchHelper
    # Sql data contains converter
    def containsConverter str
      str.gsub(/\,/,"").split(" ").join("* OR ") + "*"
    end
    
    # search data
    def search searchTerm
        connection = ActiveRecord::Base.connection
        #Prepare SQL String
        strSQL = "SELECT     EmployeeNumber AS 'samaccountName', CASE EmployeeType WHEN NULL THEN Name ELSE Name + ' - ' + EmployeeType END AS 'name', 'user' as 'recordType', Department as 'Dept', JobTitle as 'Job'"
        strSQL = strSQL + " FROM         VW_Employee AS FT_TBL"
        strSQL = strSQL + " WHERE     (Name LIKE '%"+searchTerm+"%')"
        strSQL = strSQL + " UNION"
        strSQL = strSQL + " SELECT     '' AS 'samaccountName', Department AS 'name', 'department' as 'recordType', '' as Dept, '' as 'Job'"
        strSQL = strSQL + " FROM         VW_Employee AS FT_TBL"
        strSQL = strSQL + " WHERE     (Department LIKE '%"+searchTerm+"%')"
        strSQL = strSQL + " UNION"
        strSQL = strSQL + " SELECT     '' AS 'samaccountName', JobTitle AS 'name', 'jobtitle' as 'recordType', '' as Dept, '' as 'Job'"
        strSQL = strSQL + " FROM         VW_Employee AS FT_TBL"
        strSQL = strSQL + " WHERE     (JobTitle LIKE '%"+searchTerm+"%')"
        strSQL = strSQL + " UNION"
        strSQL = strSQL + " SELECT     RoomId AS 'samaccountName', RoomDisplayName AS 'name', 'room' as 'recordType' ,'' as Dept, '' as 'Job'"
        strSQL = strSQL + " FROM         VW_Room AS FT_TBL"
        strSQL = strSQL + " WHERE     CONTAINS(RoomDisplayName, '" + containsConverter(searchTerm) + "') OR LOWER(OfficeName) LIKE '%'+ LOWER('"+searchTerm+"') + '%'"
        strSQL = strSQL + " ORDER BY recordType DESC, Name"
        
        hash_set = connection.select_all strSQL
      
        @hash_set_search = ({"rows" => hash_set}).to_json
      
        @hash_set_search
    end
end